﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Oracle.ManagedDataAccess.Client;

namespace ExportToDWGTool
{
    class ReadDataFromORCL
    {
        public OracleConnection m_conn = null;

        public Dictionary<string, JingInfo> m_DicJ = new Dictionary<string, JingInfo>();
        public Dictionary<string, SheBeiInfo> m_DicSB = new Dictionary<string, SheBeiInfo>();
        public Dictionary<string, string> m_dicDLD = new Dictionary<string, string>();

        // <电缆段ID,<通道ID,<序号,设备>>>
        public Dictionary<string, Dictionary<int, Dictionary<int, SheBeiInfo>>> m_dicDLDTD = new Dictionary<string, Dictionary<int, Dictionary<int, SheBeiInfo>>>();

        public Dictionary<string, string> m_dicType = new Dictionary<string, string>();
        public ReadDataFromORCL()
        {
            try
            {
                m_conn = new OracleConnection(OrclDBConn.mstrConn);
                m_conn.Open();
            }
            catch(Exception ex)
            {

            }
           
        }

         ~ReadDataFromORCL()
        {
            try
            {
                if (m_conn != null)
                {
                    m_conn.Close();
                    m_conn = null;
                }
            }
            catch
            {
            }
        }


         public bool ReadJingTableData()
         {
             if (m_conn == null) return false;

             try
             {          
                 OracleCommand Command = m_conn.CreateCommand();
                 string sql = "select *";

                 Command.CommandText = string.Format("{0} from DL_DLJ ", sql);
                 //执行并设置返回值
                 OracleDataReader reader = Command.ExecuteReader();  

                 while (reader.Read())
                 {
                     string wID = reader["uuid"].ToString();
                     string wMC = reader["dljname"].ToString();
                     //string wTP = reader["W_TP"].ToString();
                     string wCZ = reader["JGCAIZHI"].ToString();
                     //string wXCBH = reader["W_XCBH"].ToString();
                     string wJLB = reader["jgtype"].ToString();
                     string wJSL = reader["Jgnumber"].ToString();
                     //string wXZ = reader["W_XZ"].ToString();
                     //string wJBJ = reader["W_JBJ"].ToString();
                     string wJC = reader["jingchang"].ToString();
                     string wJK = reader["jingkuan"].ToString();
                     string wJSDS = reader["shangdingms"].ToString();
                     string wJDS = reader["jingdishen"].ToString();
                     string wJS = (double.Parse(wJSDS) + double.Parse(wJDS)).ToString();
                     //string wJNS = reader["W_JNS"].ToString();
                     string wGBCC = reader["jgchang"].ToString() + "*" + reader["jgkuan"].ToString() + "*" + reader["jggao"].ToString();
                     //string wJGN = reader["W_JGN"].ToString();
                     //string wJLX = reader["W_JLX"].ToString();
                     string wX = reader["jingdu"].ToString();
                     string wY = reader["weidu"].ToString();
                     //string wRQ = reader["W_XZSJ"].ToString();
                     if (!m_DicJ.Keys.Contains(wID))
                     {
                         JingInfo pWInfo = new JingInfo();
                         pWInfo.w_ID = wID;
                         pWInfo.w_MC = wMC;
                         //pWInfo.w_TP = wTP;
                         pWInfo.w_CZ = Typeutils.getString(int.Parse(wCZ), "井盖材料"); 
                         // pWInfo.w_XCBH = wXCBH;
                         pWInfo.w_JLB = Typeutils.getString(int.Parse(wJLB), "井盖类型"); 
                         pWInfo.w_JSL = wJSL;
                         //pWInfo.w_XZ = wXZ;
                         // pWInfo.w_JBJ = wJBJ;
                         pWInfo.w_JC = wJC;
                         pWInfo.w_JK = wJK;
                         pWInfo.w_JS = wJS;
                         pWInfo.w_JSDS = wJSDS;
                         pWInfo.w_JDS = wJDS;
                         // pWInfo.w_JNS = wJNS;
                         pWInfo.w_GBCC = wGBCC;
                        // pWInfo.w_JGN = wJGN;
                         //pWInfo.w_JLX = wJLX;
                         pWInfo.w_X = wX;
                         pWInfo.w_Y = wY;

                        // pWInfo.w_RQ = wRQ;

                         //查询断面信息

                         pWInfo.m_DicDM = new Dictionary<string, DuanMianInfo>();

                         ReadDMTableData(wID, ref pWInfo);
                         m_DicJ.Add(wID, pWInfo);
                     }

                 }

                 reader.Close();

                 
             }
             catch(Exception ex)
             {
                 return false;
             }

             return true;
         }

         public bool ReadDMTableData(string strWID, ref JingInfo pWInfo)
         {
             if (m_conn == null) return false;
             try
             {
                 
                 OracleCommand Command = m_conn.CreateCommand();
                 string sql = "select *";

                 Command.CommandText = string.Format("{0} from DL_DLJPM where DLJUUID = '{1}' ", sql, strWID);
                 //执行并设置返回值
                 IDataReader dr = null;
                 dr = Command.ExecuteReader();
     
                 while (dr.Read())
                 {
                     string sID = dr["uuid"].ToString();
                     string sMC = dr["name"].ToString();
                    // string sTP = dr["S_TP"].ToString();
                     string sFW = dr["fangwei"].ToString();
                     string sXH = dr["duanmianxuhao"].ToString();
                     string sSBJ = dr["shangbianju"].ToString();
                     string sXBJ = dr["xiabianju"].ToString();
                     string sZBJ = dr["zuobianju"].ToString();
                     string sYBJ = dr["youbianju"].ToString();
                     string sHJJ = dr["hangjianju"].ToString();
                     string sLJJ = dr["liejianju"].ToString();
                     string sH = dr["hangshu"].ToString();
                     string sL = dr["lieshu"].ToString();

                     DuanMianInfo pSInfo = new DuanMianInfo();
                     pSInfo.s_ID = sID;
                     pSInfo.s_MC = sMC;
                     //pSInfo.s_TP = sTP;
                     pSInfo.s_FW = sFW;
                     pSInfo.s_XH = sXH;
                     pSInfo.s_SBJ = sSBJ;
                     pSInfo.s_XBJ = sXBJ;
                     pSInfo.s_ZBJ = sZBJ;
                     pSInfo.s_YBJ = sYBJ;
                     pSInfo.s_H = sH;
                     pSInfo.s_L = sL;
                     pSInfo.s_HJJ = sHJJ;
                     pSInfo.s_LJJ = sLJJ;

                     //查询孔位信息
                     ReadKWTableData(sID, ref pSInfo);

                     if (pWInfo.m_DicDM != null)
                     {
                         if (!pWInfo.m_DicDM.Keys.Contains(sID))
                         {
                             pWInfo.m_DicDM.Add(sID, pSInfo);
                         }
                     }
                     else
                     {
                         pWInfo.m_DicDM = new Dictionary<string, DuanMianInfo>();
                         pWInfo.m_DicDM.Add(sID, pSInfo);
                     }
                 }

                 dr.Close();

                 return true;
             }
             catch (Exception ex)
             {

             }

             return false;
         }


         public bool ReadKWTableData(string strSID, ref DuanMianInfo pSInfo)
         {
             if (m_conn == null) return false;
             try
             {        
                 pSInfo.s_maxGJ = 0;       

                 OracleCommand Command = m_conn.CreateCommand();
                 string sql = "select *";

                 Command.CommandText = string.Format("{0} from DL_DLPMKW where DMID = '{1}' ", sql, strSID);
                 //执行并设置返回值
                 IDataReader dr = null;
                 dr = Command.ExecuteReader();
     
                 while (dr.Read())
                 {
                     string hID = dr["uuid"].ToString();
                     string hMC = dr["name"].ToString();
                     string hXCBH = dr["nummber"].ToString();
                     string hLX = dr["guankongleixing"].ToString();
                     string hGJ = dr["guankongguanjing"].ToString();
                     string hH = dr["hanghao"].ToString();
                     string hL = dr["liehao"].ToString();
                     string hCZ = dr["guankongcaizhi"].ToString();

                     if (string.IsNullOrEmpty(hGJ))
                     {
                         hGJ = "4";
                     }

                     if (string.IsNullOrEmpty(hCZ))
                     {
                         hCZ = "1";
                     }

                     if (string.IsNullOrEmpty(hLX))
                     {
                         hLX = "1";
                     }

                     hGJ = Typeutils.getString(int.Parse(hGJ), "管孔孔径");

                     hGJ = hGJ.Substring(0,hGJ.Length-2);

                     hLX = Typeutils.getString(int.Parse(hLX), "管孔类型");
                     hCZ = Typeutils.getString(int.Parse(hCZ), "管孔材质"); 

                     KongWeiInfo pHInfo = new KongWeiInfo();
                     pHInfo.h_ID = hID;
                     pHInfo.h_MC = hMC;
                     pHInfo.h_XCBH = hXCBH;
                     pHInfo.h_LX = hLX;
                     pHInfo.h_GJ = hGJ;
                     pHInfo.h_H = hH;
                     pHInfo.h_L = hL;
                     pHInfo.h_CZ = hCZ;
                   
                     if (pSInfo.s_maxGJ < double.Parse(hGJ))
                     {
                         pSInfo.s_maxGJ = double.Parse(hGJ);
                     }

                     if (pSInfo.m_DicKW != null)
                     {
                         if (!pSInfo.m_DicKW.Keys.Contains(hH + "_" + hL))
                         {
                             pSInfo.m_DicKW.Add(hH + "_" + hL, pHInfo);
                         }
                     }
                     else
                     {
                         pSInfo.m_DicKW = new Dictionary<string, KongWeiInfo>();
                         pSInfo.m_DicKW.Add(hH + "_" + hL, pHInfo);
                     }

                 }
                 dr.Close();

                 return true;
             }
             catch (Exception ex)
             {
             }

             return false;
         }


         public bool ReadDL_DLDGLBData()
         {
             if (m_conn == null) return false;
             try
             {

                 ReadDL_SJZDBData("设备类型");


                 OracleCommand Command = m_conn.CreateCommand();
                 string sql = "select *";

                 Command.CommandText = string.Format("{0} from DL_DLDGLB ", sql);
                 //执行并设置返回值
                 OracleDataReader reader = Command.ExecuteReader();

                 while (reader.Read())
                 {
                     string dID = reader["UUID"].ToString();  //设备序号
                     string dXH = reader["XUHAO"].ToString();  //通道序号
                     string dNum = reader["NUMMBER"].ToString(); //现场编号
                     string dTDnum = reader["TONGDAONUMMBER"].ToString(); //通道编号

                     string dTDType = reader["TDTYPE"].ToString();    //通道类型    

                     ////if (dNum!="34492bbee66c4b05b27b2fad11eb3714")
                     ////{
                     ////    continue;
                     ////}
           

                     if (m_dicDLDTD.Keys.Contains(dNum))
                     {
                         Dictionary<int, Dictionary<int, SheBeiInfo>> dicTD = m_dicDLDTD[dNum];

                         if (!dicTD.Keys.Contains(int.Parse(dXH)))
                         {
                             Dictionary<int, SheBeiInfo> dicSB = new Dictionary<int, SheBeiInfo>();
                             ReadDL_TDGLBData(dTDnum, ref dicSB);

                             dicTD.Add(int.Parse(dXH), dicSB);
                         }
                     }
                     else
                     {
                         Dictionary<int, Dictionary<int, SheBeiInfo>> dicTD = new Dictionary<int, Dictionary<int, SheBeiInfo>>();
                         Dictionary<int, SheBeiInfo> dicSB = new Dictionary<int, SheBeiInfo>();
                         ReadDL_TDGLBData(dTDnum, ref dicSB);

                         dicTD.Add(int.Parse(dXH), dicSB);

                         m_dicDLDTD.Add(dNum, dicTD);
                     }

                 }

                 reader.Close();

                 ReadDL_DLDBData();


             }
             catch (Exception ex)
             {
                 return false;
             }

             return true;
         }


         public bool ReadDL_TDGLBData(string strID,ref Dictionary<int,SheBeiInfo> dicSB)
         {
             if (m_conn == null) return false;
             try
             {

                 OracleCommand Command = m_conn.CreateCommand();
                 string sql = "select *";

                 Command.CommandText = string.Format("{0} from DL_TDGLB where NUMMBER = '{1}'order by XUHAO ", sql, strID);
                 //执行并设置返回值
                 OracleDataReader reader = Command.ExecuteReader();

                 while (reader.Read())
                 {
                     string sXH = reader["XUHAO"].ToString();  //设备序号
                     string sTDNum = reader["NUMMBER"].ToString(); //现场编号
                     string sNum = reader["SHEBEINUMMBER"].ToString(); //设备编号

                     string sType = reader["SHEBEITYPE"].ToString();

                     string sJingDu = reader["JINGDU"].ToString();    //经度
                     string sWeiDu = reader["WEIDU"].ToString();    //纬度

                     string sTDType = reader["TONGDAOTYPE"].ToString(); //类型

                     SheBeiInfo pInfo = new SheBeiInfo();
                     pInfo.s_ID = sNum;
                     pInfo.s_TDype = sTDType;
                     pInfo.s_TID = sTDNum;
                     pInfo.s_X = sJingDu;
                     pInfo.s_Y = sWeiDu;
                     pInfo.s_XH = sXH;
                     pInfo.s_Type = m_dicType[sType];

                     if (sType!="10")
                     {
                         string strTbName = GetTbNameByType(int.Parse(sType));

                         ReadDL_SBNameFromTB(strTbName, sNum, ref pInfo.s_Name);
                     }

                   

                     dicSB.Add(int.Parse(sXH), pInfo);

                     if(!m_DicSB.Keys.Contains(sNum))
                     {
                         m_DicSB.Add(sNum, pInfo);
                     }
                    
                 }

                 reader.Close();

             }
             catch (Exception ex)
             {
                 return false;
             }

             return true;
         }
 
         
        public string GetTbNameByType(int iType)
        {
                string strTBName = "";
                     switch(iType)
                     {
                         case 0:
                             strTBName = "DL_BDZ";
                             break;
                         case 1:
                              strTBName = "DL_SBBYQ";
                             break;
                         case 2:
                              strTBName = "DL_SBFZX";
                              break;
                         case 3:
                              strTBName = "DL_SBGT";
                              break;
                         case 4:
                              strTBName = "DL_SBHWG";
                              break;
                         case 5:
                              strTBName = "DL_SBKGZ";
                              break;
                         case 6:
                              strTBName = "DL_SBPDS";
                              break;
                         case 7:
                              strTBName = "DL_SBXB";
                              break;
                         case 8:
                              strTBName = "DL_SBXN";
                              break;
                         case 9:
                              strTBName = "DL_SBGD";
                              break;
                         //case 10:
                         //     strTBName = "DL_DLJ";
                         //     break;

                     }

                     return strTBName;

           }

         public bool ReadDL_SJZDBData(string strType)
         {
             if (m_conn == null) return false;
             try
             {

                 OracleCommand Command = m_conn.CreateCommand();
                 string sql = "select *";

                 Command.CommandText = string.Format("{0} from DL_SJZDB where type = '{1}' ", sql, strType);
                 //执行并设置返回值
                 OracleDataReader reader = Command.ExecuteReader();

                 while (reader.Read())
                 {
                     string sTYpe = reader["BMKEY"].ToString();  //名称
                     string sName = reader["NAME"].ToString();  //名称

                     m_dicType.Add(sTYpe, sName);
                 }

                 reader.Close();

             }
             catch (Exception ex)
             {
                 return false;
             }

             return true;
         }


         public bool ReadDL_DLDBData()
         {
             if (m_conn == null) return false;
             try
             {

                 OracleCommand Command = m_conn.CreateCommand();
                 string sql = "select *";

                 Command.CommandText = string.Format("{0} from DL_DLD ", sql);
                 //执行并设置返回值
                 OracleDataReader reader = Command.ExecuteReader();

                 while (reader.Read())
                 {
                     string sName = reader["NAME"].ToString();  

                     string sID = reader["UUID"].ToString(); 

                     m_dicDLD.Add(sID,sName);
                     
                 }

                 reader.Close();


             }
             catch (Exception ex)
             {
                 return false;
             }

             return true;
         }


         public bool ReadDL_SBNameFromTB(string strTB,string strID,ref string strName)
         {
             if (m_conn == null) return false;
             try
             {

                 OracleCommand Command = m_conn.CreateCommand();
                 string sql = "select *";

                 Command.CommandText = string.Format("{0} from {1} where UUID='{2}' ", sql,strTB,strID);
                 //执行并设置返回值
                 OracleDataReader reader = Command.ExecuteReader();

                 while (reader.Read())
                 {
                     string sName = reader["NAME"].ToString();
                     strName = sName;

                 }

                 reader.Close();

             }
             catch (Exception ex)
             {
                 return false;
             }

             return true;
         }
     
    }
}
